package jt.classic.system.objectquery;

import jt.classic.system.ISystem;
import jt.classic.system.object.IObject;
import jt.classic.system.user.IUser;
import jt.classic.system.wordbook.XWordbook;
import jt.classic.system.wordbook.XWordbookMain;
import jt.classic.system.wordbook.XWordbookQuery;
import jt.classic.system.workflow.WObject;

import org.limp.basework.Table;

import org.limp.mine.DateTrimmer;
import org.limp.mine.HTMLTool;
import org.limp.mine.StringTool;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * 对象实体数据集合查询工具类
 *
 * @author mido
 *
 */
public final class ObjectARGTool {
    /**
     * 对象数据列表查询 count sql 编写
     * @param query
     * @param param
     * @return String
     */
    public static String getListSQL4Query(IUser user, WObject obj,
        ObjectQuery query, Map param) {
        /*拼写object表查询sql*/
        StringBuffer sql1 = new StringBuffer();

        String okey = obj.ikey();
        String qname = StringTool.checkString(param.get("qname"));
        String qcreatetime = StringTool.checkString(param.get("qcreatetime"));
        String qupdatetime = StringTool.checkString(param.get("qupdatetime"));

        StringBuffer where = new StringBuffer();

        where.append(" AND OBJ_KEY = '" + okey + "'");

        if ((query != null) && (query.isession() == 1)) {
            if (ISystem.matchrole(user, "groupadmin")) {
                where.append(" AND (OBJ_CREATORID = '" + user.id() + "'");
                where.append(" OR OBJ_UNITNUMBER LIKE '" +
                    user.igroup().inumber() + "%')");
            } else {
                where.append(" AND OBJ_CREATORID = '" + user.id() + "'");
            }

            //param.put("qcreatorid", user.id());
        }

        if ((query != null) &&
                (!query.icreatestime().equals("") &&
                !query.icreateetime().equals(""))) {
            where.append(" AND OBJ_CREATETIME >= '" +
                DateTrimmer.formatDateForVarchar(query.icreatestime()) +
                "000000' AND  OBJ_CREATETIME <= '" +
                DateTrimmer.formatDateForVarchar(query.icreateetime()) +
                "235959'");

            //param.put("qcreatorid", user.id());
        } else if ((query != null) &&
                (!query.icreatestime().equals("") &&
                query.icreateetime().equals(""))) {
            where.append(" AND AND OBJ_CREATETIME >= '" +
                DateTrimmer.formatDateForVarchar(query.icreatestime()) +
                "000000'");

            //param.put("qcreatorid", user.id());
        } else if ((query != null) &&
                (query.icreatestime().equals("") &&
                !query.icreateetime().equals(""))) {
            where.append(" AND OBJ_CREATETIME <= '" +
                DateTrimmer.formatDateForVarchar(query.icreateetime()) +
                "235959'");

            //param.put("qcreatorid", user.id());
        }

        if ((query != null) && (!query.ipurview().equals(""))) {
            if (query.ipurview().equals("groupadmin") &&
                    (user.irole("groupadmin") != null) &&
                    (user.igroup() != null)) {
                where.append(" AND OBJ_UNITID = '" + user.igroup().id() + "' ");
            }
        }

        if (!qname.equals("")) {
            where.append(" AND OBJ_NAME LIKE '%" + qname + "%' ");
        }

        param.put("qname", qname);

        if (!qcreatetime.equals("")) {
            where.append(" AND OBJ_CREATETIME LIKE '%" +
                DateTrimmer.formatDateForVarchar(qcreatetime) + "%' ");
        }

        param.put("qcreatetime", qcreatetime);

        if (!qupdatetime.equals("")) {
            where.append(" AND OBJ_UPDATETIME LIKE '%" + qupdatetime + "%' ");
        }

        param.put("qupdatetime", qupdatetime);

        if (query == null) {
            sql1.append(
                "SELECT ISYS_OBJECT.* FROM ISYS_OBJECT WHERE OBJ_KEY = '" +
                okey + "' ");
            sql1.append(where.toString());

            return sql1.toString();
        }

        StringBuffer sql = new StringBuffer("SELECT DISTINCT(A0.ID) FROM ");

        List tables = query.iobject().itables();

        /*有效的待拼写sql的关联表map*/
        Map validTableMap = new HashMap();

        for (int j = 0; j < tables.size(); j++) {
            /*是否拼写有效的sql语句*/
            boolean validTableSQL = false;

            Table table = (Table) tables.get(j);
            List querylist = query.iqueryitems(table.getName());

            if ((querylist != null) && !querylist.isEmpty()) {
                XWordbookMain wbmain = XWordbookMain.getInstance();

                XWordbookQuery wbquery = wbmain.getWordbookQuery("compare-mode");

                for (int i = 0; i < querylist.size(); i++) {
                    ObjectQueryItem item = (ObjectQueryItem) querylist.get(i);

                    String cvalue = StringTool.checkString(param.get(
                                item.icolumn().getName()), "");

                    param.put(item.icolumn().getName(), cvalue);

                    /*checkbox*/
                    if (item.itype().equals("checkbox") && !cvalue.equals("")) {
                        if (cvalue.indexOf(",") > -1) {
                            validTableSQL = true;

                            break;
                        } else {
                            validTableSQL = true;

                            break;
                        }
                    } else if (!item.itype().equals("checkbox") &&
                            !cvalue.equals("")) {
                        validTableSQL = true;

                        break;
                    }
                }
            }

            if (!validTableSQL) {
                List restraintlist = query.irestraintitems(table.getName());

                if ((restraintlist != null) && !restraintlist.isEmpty()) {
                    for (int i = 0; i < restraintlist.size(); i++) {
                        ObjectQueryItem item = (ObjectQueryItem) restraintlist.get(i);

                        String defaultValue = StringTool.checkString(item.iparam());

                        param.put(item.icolumn().getName(), defaultValue);

                        if (!defaultValue.equals("")) {
                            validTableSQL = true;

                            break;
                        }
                    }
                }
            }

            if (validTableSQL) {
                validTableMap.put(table.getName(), table.getName());
            }
        }

        int count = 0;

        for (int j = 0; j < tables.size(); j++) {
            Table table = (Table) tables.get(j);

            if (validTableMap.containsKey(table.getName())) {
                List querylist = query.iqueryitems(table.getName());

                StringBuffer tableSelectSQL = new StringBuffer();

                if (count > 0) {
                    sql.append(", ");
                }

                tableSelectSQL.append(
                    "(SELECT DISTINCT(ISYS_OBJECT.ID) FROM ISYS_OBJECT LEFT JOIN ISYS_OBJECT_RELATION ON ISYS_OBJECT.ID = ISYS_OBJECT_RELATION.ID");
                tableSelectSQL.append(" LEFT JOIN " + table.getName() +
                    " ON ISYS_OBJECT_RELATION.OBJ_PKID = " + table.getName() +
                    "." + table.getPrimaryKey()[0]);

                /*存在数据访问权限控制*/
                if (!query.ipurview().equals("") &&
                        !query.ipurview().equals("groupadmin") &&
                        (user.irole(query.ipurview()) != null)) {
                    tableSelectSQL.append(
                        " LEFT JOIN ISYS_PURVIEW ON ISYS_OBJECT.ID = ISYS_PURVIEW.OBJ_ID WHERE ISYS_PURVIEW.ROLE_ID = '" +
                        user.irole(query.ipurview()).id() +
                        "' AND ISYS_PURVIEW.ROLE_TYPE = 'purview' AND ISYS_PURVIEW.PURVIEW > 0 AND ISYS_PURVIEW.OBJ_TYPE = 'object' ");

                    tableSelectSQL.append(
                        " AND ISYS_OBJECT_RELATION.OBJ_TABLE = '" +
                        table.getName() + "'");
                } else {
                    tableSelectSQL.append(
                        " WHERE ISYS_OBJECT_RELATION.OBJ_TABLE = '" +
                        table.getName() + "'");
                }

                tableSelectSQL.append(where);

                XWordbookMain wbmain = XWordbookMain.getInstance();

                XWordbookQuery wbquery = wbmain.getWordbookQuery("compare-mode");

                if ((querylist != null) && !querylist.isEmpty()) {
                    for (int i = 0; i < querylist.size(); i++) {
                        ObjectQueryItem item = (ObjectQueryItem) querylist.get(i);

                        String defaultValue = StringTool.checkString(item.iparam());

                        String cvalue = StringTool.checkString(param.get(
                                    item.icolumn().getName()), "");

                        //param.put(item.icolumn().getName(), cvalue);
                        int paramType = 0;

                        /*checkbox*/
                        if (item.itype().equals("checkbox") &&
                                !cvalue.equals("")) {
                            if (cvalue.indexOf(",") > -1) {
                                paramType = 2;
                            } else {
                                paramType = 1;
                            }
                        } else if (!item.itype().equals("checkbox") &&
                                !cvalue.equals("")) {
                            paramType = 1;
                        }

                        if (paramType == 1) {
                            XWordbook wb = wbquery.query(item.imode());

                            if (item.icolumn().getType().toString().equals(StringTool.VARCHAR.toString())) {
                                /*like*/
                                if (wb.getName().toLowerCase().equals("like")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '%" +
                                        cvalue + "%' ");
                                } else if (wb.getName().toLowerCase().equals("llike")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '%" +
                                        cvalue + "' ");
                                } else if (wb.getName().toLowerCase().equals("rlike")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '" +
                                        cvalue + "%' ");
                                } else {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " " +
                                        wb.getName() + " '" + cvalue + "' ");
                                }
                            } else {
                                tableSelectSQL.append(" AND " +
                                    item.icolumn().getName() + " " +
                                    wb.getName() + " " + cvalue + " ");
                            }
                        } else if (paramType == 2) {
                            XWordbook wb = wbquery.query(item.imode());

                            String[] params = cvalue.split(",");

                            tableSelectSQL.append(" AND (");

                            for (int k = 0; k < params.length; k++) {
                                if (item.icolumn().getType().toString().equals(StringTool.VARCHAR.toString())) {
                                    /*like*/
                                    if (wb.getName().toLowerCase().equals("like")) {
                                        tableSelectSQL.append(item.icolumn()
                                                                  .getName() +
                                            " LIKE '%" + params[k] + "%' ");
                                    } else if (wb.getName().toLowerCase()
                                                     .equals("llike")) {
                                        tableSelectSQL.append(" AND " +
                                            item.icolumn().getName() +
                                            " LIKE '%" + cvalue + "' ");
                                    } else if (wb.getName().toLowerCase()
                                                     .equals("rlike")) {
                                        tableSelectSQL.append(" AND " +
                                            item.icolumn().getName() +
                                            " LIKE '" + cvalue + "%' ");
                                    } else {
                                        tableSelectSQL.append(item.icolumn()
                                                                  .getName() +
                                            " " + wb.getName() + " '" +
                                            params[k] + "' ");
                                    }
                                } else {
                                    tableSelectSQL.append(item.icolumn()
                                                              .getName() + " " +
                                        wb.getName() + " " + params[k] + " ");
                                }

                                if (k != (params.length - 1)) {
                                    tableSelectSQL.append(" OR ");
                                }
                            }

                            tableSelectSQL.append(") ");
                        }
                    }
                }

                List restraintlist = query.irestraintitems(table.getName());

                if ((restraintlist != null) && !restraintlist.isEmpty()) {
                    for (int i = 0; i < restraintlist.size(); i++) {
                        ObjectQueryItem item = (ObjectQueryItem) restraintlist.get(i);

                        String defaultValue = StringTool.checkString(item.iparam());

                        if (defaultValue.startsWith("@")) {
                            defaultValue = StringTool.checkString(param.get(
                                        defaultValue.substring(1)));
                        }

                        //param.put(item.icolumn().getName(), defaultValue);
                        if (!defaultValue.equals("")) {
                            XWordbook wb = wbquery.query(item.imode());

                            if (item.icolumn().getType().toString().equals(StringTool.VARCHAR.toString())) {
                                /*like*/
                                if (wb.getName().toLowerCase().equals("like")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '%" +
                                        defaultValue + "%' ");
                                } else if (wb.getName().toLowerCase().equals("llike")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '%" +
                                        defaultValue + "' ");
                                } else if (wb.getName().toLowerCase().equals("rlike")) {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " LIKE '" +
                                        defaultValue + "%' ");
                                } else {
                                    tableSelectSQL.append(" AND " +
                                        item.icolumn().getName() + " " +
                                        wb.getName() + " '" + defaultValue +
                                        "' ");
                                }
                            } else {
                                tableSelectSQL.append(" AND " +
                                    item.icolumn().getName() + " " +
                                    wb.getName() + " " + defaultValue + " ");
                            }
                        }
                    }
                }

                tableSelectSQL.append(") A" + count);

                sql.append(tableSelectSQL);

                count++;
            }
        }

        if (count == 0) {
            sql.append(" ISYS_OBJECT A0 ");

            if (!query.ipurview().equals("") &&
                    !query.ipurview().equals("groupadmin") &&
                    (user.irole(query.ipurview()) != null)) {
                sql.append(
                    " LEFT JOIN ISYS_PURVIEW ON A0.ID = ISYS_PURVIEW.OBJ_ID WHERE ISYS_PURVIEW.ROLE_ID = '" +
                    user.irole(query.ipurview()).id() +
                    "' AND ISYS_PURVIEW.ROLE_TYPE = 'purview' AND ISYS_PURVIEW.PURVIEW > 0 AND ISYS_PURVIEW.OBJ_TYPE = 'object' ");
                sql.append(where);
            } else {
                sql.append(" WHERE 1=1 ");
                sql.append(where);
            }
        } else if (count <= 1) {
            //do nothing
        } else {
            sql.append(" WHERE ");

            for (int i = 0; i < count; i++) {
                if (i == (count - 1)) {
                    break;
                }

                sql.append(("A" + i + ".ID = ") + ("A" + (i + 1) + ".ID "));

                if (i < (count - 2)) {
                    sql.append(" AND ");
                }
            }
        }

        return sql.toString();
    }

    /**
     * List 结果集
     * <p>
     *   <div>List结构-->{[Map],[Map]}</div>
     *   <div>
     *        stamp结构-->id:PKID;name:PKNAME;alt:PKMEMO;
     *        other:OTHER;title:数据来源;type:radio(或者checkbox);
     *        cid:sjly;size:5
     *   </div>
     *   <div>return Table HTML</div>
     *   <div>table class is 'intable'</div>
     *   <div>tr class is 'intr'</div>
     *   <div>td class is 'intd'</div>
     *   <div>checkbox/radio onclick is 'handleThizz'</div>
     *   <div>checkbox/radio id is cid + "_chk_" + j</div>
     * </p>
     * @param list 结果集
     * @param viewlist 显示项集合
     * @param stamp 特征值
     * @return String context
     */
    public static String rangeObjectList(List list, List viewlist,
        String stamp, String context) {
        StringBuffer temp = new StringBuffer();

        String[] splitstr = StringTool.checkString(stamp).split(";");
        String kid = "";
        String kname = "";
        String kmemo = "";
        String kother = "";
        String title = "";
        String type = "";
        String cid = "";
        int size = 5;

        for (int i = 0; i < splitstr.length; i++) {
            String str = StringTool.checkString(splitstr[i]);

            if (!str.equals("")) {
                if ((str.lastIndexOf(":") != -1) &&
                        (str.lastIndexOf(":") < str.length())) {
                    /*for id*/
                    if (str.substring(0, str.lastIndexOf(":")).equals("id")) {
                        kid = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for name*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("name")) {
                        kname = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for alt*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("alt")) {
                        kmemo = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for other*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("other")) {
                        kother = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for title*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("title")) {
                        title = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for type*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("type")) {
                        type = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for cid*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("cid")) {
                        cid = str.substring(str.lastIndexOf(":") + 1,
                                str.length());
                    }
                    /*for size*/
                    else if (str.substring(0, str.lastIndexOf(":")).equals("size")) {
                        try {
                            size = Integer.parseInt(str.substring(str.lastIndexOf(
                                            ":") + 1, str.length()));
                        } catch (Exception ex) {
                            System.err.println(
                                "Parameter 'size' must be Integer.");
                        }

                        if (size <= 0) {
                            size = 5;
                        }
                    }
                }
            }
        }

        if ((list != null) && !list.isEmpty()) {
            temp.append("<table class=intable title=" + title + ">");

            int count = ((list.size() % size) > 0) ? ((list.size() / size) + 1)
                                                   : (list.size() / size);

            for (int i = 0; i < count; i++) {
                temp.append("<tr class=intr>");

                for (int j = i * size; j < ((i * size) + size); j++) {
                    if (j < list.size()) {
                        Map info = (Map) list.get(j);
                        String id = (String) info.get("ID");
                        String name = (String) info.get("OBJ_NAME");
                        String creatorname = (String) info.get(
                                "OBJ_CREATORNAME");
                        String creatortime = (String) info.get("OBJ_CREATETIME");
                        String updatetime = (String) info.get("OBJ_UPDATETIME");

                        String url = context +
                            "/app/info.do?method=face2directory&id=" + id;

                        temp.append("<td class=intd valign=top title='" +
                            creatorname + "'>");
                        temp.append(
                            "<div style=\"padding:5px;width:100px;\" onmouseover=\"this.className='inimgover'\" " +
                            "onmouseout=\"this.className='inimgout'\">");

                        temp.append("<div class=\"padding:5px;\"><a href=\"" +
                            url + "\" target=\"_blank\">" + name +
                            "</a></div>");

                        temp.append("<div class=\"padding:5px;\"></div>");

                        temp.append("</div>");
                        temp.append("</td>");
                    } else {
                        temp.append("<td class=intd>");
                        temp.append(
                            "<div style=\"padding:5px;width:100px;\">&nbsp;</div>");
                        temp.append("</td>");
                    }
                }

                temp.append("</tr>");
            }

            temp.append("</table>");
        }

        return temp.toString();
    }
}
